﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MixScript.BLL;
using System.Data;

namespace MixScript.BLL.FI
{
    public class cJournal : cBaseBLL
    {

        public cJournal()
        { }

        public DataSet CreateTable(string userName, int editID)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string sql = @"usp_createTempTable '{0}',{1}";
            dt = Dbserver.ExecuteQuery(string.Format(sql, "temp_" + userName, editID), "Sub");
            ds.Tables.Add(dt);
            dt = new DataTable();
            dt = Dbserver.ExecuteQuery(string.Format("select * from accsliphdr where slipid = {0}", editID), "Header");
            ds.Tables.Add(dt);
            return ds;

        }
        public DataTable InsertSub(int No, string headerid, string acctnum, string desc, string dbcd
           , string currency, string kurs, string rate, string amount, string valconvert
           , string invoice, string process, string subsidiary, string sub_date, string sub_terms
           , string poinvoiceno, string poinvoicedate, string username)
        {
            string sql = @"INSERT INTO {17} 
                        ([No],[header_id],[acctnum],[desc],[dbcd]
                        ,[currency],[kurs],[rate],[amount],[valconvert]
                        ,[invoice],[process],[subsidiary],[sub_date]
                        ,[sub_terms],[poinvoiceno],[poinvoicedate],so_id)
                            VALUES
                            ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}'
                            ,'{10}','{11}','{12}','{13}','{14}','{15}','{16}','0')";

            Dbserver.openConn();
            Dbserver.ExecuteNonQuery(string.Format(sql, No, headerid, acctnum, desc, dbcd, currency
           , kurs, rate, amount, valconvert, invoice, process
           , subsidiary, sub_date, sub_terms, poinvoiceno
           , poinvoicedate, "temp_" + username));
            Dbserver.CommitAndCloseConn();
            return GetSubAll(username);

        }
        public void InsertSubPembanding(int headerid, string acctnum, string desc, string dbcd
           , string currency, string kurs, string rate, string amount, string valconvert
           , string invoice, string process, string subsidiary, string sub_date, string sub_terms
           , string poinvoiceno, string poinvoicedate)
        {
            string sql = @"INSERT INTO accslipdet
                        ([header_id],[acctnum],[desc],[dbcd]
                        ,[currency],[kurs],[rate],[amount],[valconvert]
                        ,[invoice],[process],[subsidiary],[sub_date]
                        ,[sub_terms],[poinvoiceno],[poinvoicedate],so_id)
                            VALUES
                            ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}'
                            ,'{10}','{11}','{12}','{13}','{14}','{15}','1')";

            Dbserver.openConn();
            Dbserver.ExecuteNonQuery(string.Format(sql, headerid, acctnum, desc, dbcd, currency
           , kurs, rate, amount, valconvert, invoice, process
           , subsidiary, sub_date, sub_terms, poinvoiceno
           , poinvoicedate));
            Dbserver.CommitAndCloseConn();

        }
        public DataTable GetSubAll(string username)
        {
            DataTable dt = new DataTable();
            dt = Dbserver.ExecuteQuery(string.Format(@"SELECT No, [header_id],[acctnum],[desc],[dbcd],[currency],[kurs],[rate]  
                        ,[amount],case dbcd when 'd' then [valconvert] end debit,
                        case dbcd when 'c' then [valconvert] end credit,[so_id],[fg_id]  
                        ,[invoice],[process],[subsidiary],
                        case CAST([sub_date]  AS DATETIME) when '1900-01-01 00:00:00.000' then '' 
                        else CAST([sub_date]  AS DATETIME) end
                        sub_date
                        ,[sub_terms],[poinvoiceno],
                        case CAST([poinvoicedate]  AS DATETIME) when '1900-01-01 00:00:00.000' then '' 
                        else CAST([poinvoicedate]  AS DATETIME) end
                        poinvoicedate
                        FROM {0} ", "temp_" + username), "Sub");
            return dt;
        }
        public DataTable GetSub(string username, int No)
        {
            DataTable dt = new DataTable();
            dt = Dbserver.ExecuteQuery(string.Format(@"select *,case CAST([sub_date]  AS DATETIME) when '1900-01-01 00:00:00.000' then '' 
                                    else CAST([sub_date]  AS DATETIME) end,case CAST([poinvoicedate]  AS DATETIME) when '1900-01-01 00:00:00.000' then '' 
                                    else CAST([poinvoicedate]  AS DATETIME) end from {0} where No = {1}", "temp_" + username, No), "Sub");
            return dt;
        }
        public void Delete(string username, int No)
        {
            Dbserver.openConn();
            Dbserver.ExecuteNonQuery(string.Format("Delete {0} where No = {1}", "temp_" + username, No));
            Dbserver.CommitAndCloseConn();
        }
        public int Save(int id, string slipcode, string slipnum, string slipcurr, string slipdesc, DateTime slipdate, string username)
        {
            string sql = @"InsertJurnalHeader '{0}','{1}','{2}','{3}','{4}','{5}','{6}'";

            DataTable dt = new DataTable();
            dt = Dbserver.ExecuteQuery(string.Format(sql, id, slipcode, slipnum, slipcurr, slipdesc, slipdate, "temp_" + username), "insert");
            id = (Int32)dt.Rows[0].ItemArray[0];
            return id;
        }

    }
}
